/*********************************************************************************
 *      Copyright:  (C) 2025 iot25<lingyun@email.com>
 *                  All rights reserved.
 *
 *       Filename:  sqlite_database.c
 *    Description:  This file sqlite_database.c
 *                 
 *        Version:  1.0.0(01/05/25)
 *         Author:  iot25 <lingyun@email.com>
 *      ChangeLog:  1, Release initial version on "01/05/25 09:33:56"
 *                 
 *******************************************************************************/
#include<stdio.h>
#include<sqlite3.h>
#include<stdlib.h>
#include<string.h>

#include"logger.h"
#include"sqlite_database.h"
#include"pack_data.h"


int create_db(char *db_name, sqlite3 *db)
{
	int 				result;
	char				sql[256];
	char				*errmsg;

	if( !db_name )
	{
		log_error("invalid_argument!\n");
		return -1;
	}
	//创建sqlite数据表，并打开连接
	result = sqlite3_open(db_name, &db);
	if(result != SQLITE_OK)
	{
		log_error("Failed to open sqlite database: %s\n", sqlite3_errmsg(db));
		return -1;
	}
	else
	{
		log_debug( "Open sqlite database successfully!\n");
	}

	//创建表格ds18b20_data
	snprintf(sql, sizeof(sql), "create table if not exists ds18b20_data (rowid INTEGER PRIMARY KEY,device_id TEXT NOT NULL,temperature	FLOAT NUT NULL,time_now	TEXT NOT NULL);");
	result = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if(result != SQLITE_OK)
	{
		log_error("Creat table ds18b20_data failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
		log_debug("Creat table ds18b20_data sucessfully!\n");
		return 0;
}

	//插入数据
int insert_data(sqlite3 *db, pack_t *pack)
{
	char					sql[512];
	int						result=0;
	char					*errmsg = NULL;

	if( NULL == db || NULL == pack )
	{
		log_error("invalid_argument!\n");
		return -1;
	}

	snprintf(sql,sizeof(sql), 
			"INSERT INTO ds18b20_data (device_id, temperature, time_now) VALUES ('%s', %f,'%s');", 
			pack->dev_id, pack->temperature, pack->dev_time);
	
	result = sqlite3_exec(db, sql, NULL, 0, &errmsg);
	if(result != SQLITE_OK)
	{
		log_error("Insert data failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
		log_debug("Insert data to table ds18b20_data sucessfully!\n");
		return 0;
}

	//删除数据
int delete_dbdata(sqlite3 *db)
{
	char					*errmsg = NULL;
	char 					*sql;
	char					**dbResult;
	int						nRow;
	int						nColumn;
	int						result;

	sql = "delete from ds18b20_data WHERE rowid = (SELECT MIN(rowid) FROM ds18b20_data);;";

	result = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, &errmsg);
	if(result != SQLITE_OK)
	{
        log_error("Delete data from ds18b20_data failure: %s\n", errmsg);
        sqlite3_free(errmsg);
		return -1;
	}
	log_debug("Delete data from table ds18b20_data sucessfully!\n");
    return 0;
}

	//判断数据表中是否为空
int is_table_empty(sqlite3 *db)
{
	char					*sql;
	int						result = 0;
	char					**dbResult = NULL;
	int						nRow;
	int						nColumn;
	char					*errmsg;

	sql = "SELECT * from ds18b20_data;";
	
	result = sqlite3_get_table(db, sql, &dbResult, &nRow,  &nColumn, &errmsg);
	if(result != SQLITE_OK)
	{
		log_error("Judge data exists error!\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	else
	{
		if(nRow > 0)
		{
			log_debug(" Table ds18b20_data is not empty nRow is %d\n", nRow);
			return 1;
		}
		else
		{
			log_debug("Table ds18b20_data is empty!\n");
			return -2;
		}

	}
}

//弹出数据表中的第一条消息，并存放在变量中，为上报到服务器端做准备
int get_db_record(sqlite3 *db, pack_t *pack)
{
	char					*sql;
	int						result = 0;
	char					*errmsg;
	char					**dbResult = NULL;
	int						nRow;
	int						nColumn;
	int						index;

	sql = "SELECT * FROM ds18b20_data ORDER BY rowid ASC LIMIT 1;";

	result = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, &errmsg);
	if(result != SQLITE_OK)
	{
		log_error("Failed to execute SQL statement: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	else
	{
		index = nColumn + 1;
		strncpy(pack->dev_id, dbResult[index], 40);
		log_debug("Device_id: %s\n", pack->dev_id);
		index++;
		pack->temperature = (float)atof(dbResult[index]);
		log_debug("Temperature: %f\n", pack->temperature);
		index++;
		strncpy(pack->dev_time, dbResult[index], 40);
	}

	sqlite3_free_table(dbResult);

	return 0;
}

